package com.hsnn.medstgmini.pro.project.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.DrugPriceQuoteDao;
import com.hsnn.medstgmini.pro.project.model.form.ProdProjectForm;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.MapToBeanUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.PinYin;

/**
 * @category 药品价格申报
 * @author wagnbing
 * @date 2015年8月26日
 */
@Repository
public class DrugPriceQuoteDaoImpl implements DrugPriceQuoteDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;

	

	/**
	 * @category 查询项目列表
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugPriceQuoteProjList(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.proj_code) AS projCode, MAX(a.name) name, MAX(a.proj_cat) AS projCat, MAX(a.status) status, DATE_FORMAT( MAX(a.create_datetime), '%Y-%m-%d %H:%i:%S' ) AS createDatetime, DATE_FORMAT(MAX(a.starttime), '%Y-%m-%d %H:%i:%S') AS starttime, DATE_FORMAT(MAX(a.endtime), '%Y-%m-%d %H:%i:%S') AS endtime, MAX(a.proj_intro) AS projIntro, MAX(a.remark) remark  FROM view_bidproject a JOIN bid_projprodcompany b ON a.proj_id = b.proj_id JOIN std_compprofile c ON b.prod_comp_id = c.comp_id AND c.status = 1 LEFT JOIN bid_projcompprofile d ON a.proj_id = d.proj_id AND c.compprofile_id = d.compprofile_id WHERE 1 = 1 AND a.proj_cat = 1 AND a.status = 1 AND a.starttime <= SYSDATE() AND A.endtime >= SYSDATE() AND b.prod_comp_id = :compId GROUP BY a.proj_id ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projCode = form.getProjCode();
			if (StringUtils.isNotBlank(projCode)) {
				sb.append(" AND a.proj_code = :projCode ");
				params.put("projCode", projCode);
			}
			String name = form.getName();
			if (StringUtils.isNotBlank(name)) {
				sb.append(" AND a.name LIKE :name ");
				params.put("name", "%" + name + "%");
			}
		}

		sb.append(" order by a.create_datetime desc ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	}
	
	/**
	 * @category 获取所有药品列表
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getDrugPriceQuoteList(Pagination page, SysUser user) {
		String qualityspecName = page.getConditions().get("qualityspecName");
		String qualityspecCode = page.getConditions().get("qualityspecCode");
		String projId = page.getConditions().get("projId");
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT bp.proj_proddrug_id projProddrugId,bp.proj_id projId,							")
		   .append("    DATE_FORMAT(bp.decrypt_datetime, '%Y-%m-%d %H:%i:%S' )	decryptDatetime,				")
		   .append("    sd.qualityspec_name qualityspecName,bp.decrypt_price decryptPrice,              		")
		   .append("	sd.qualityspec_code qualityspecCode,ddf.form_name formName,dds.spec_name specName,		")
		   .append("	sp.factor factor,dp.packunit_name packunitName,dd.material_name materialName, 			")
		   .append("    bp.encrypt_price encryptPrice,bp.decrypt_status decryptStatus FROM bid_projproddrug bp	")
		   .append("	LEFT JOIN std_proddruginfo sp ON bp.prod_id=sp.prod_id 									")
		   .append("	LEFT JOIN std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id				")
		   .append("	LEFT JOIN dic_drugspec dds ON dds.spec_id = sd.spec										")
		   .append("	LEFT JOIN dic_drugform ddf ON ddf.form_id = sd.form										")
		   .append("	LEFT JOIN dic_packunit dp ON dp.packunit_id = sp.unit									")
		   .append("	LEFT JOIN dic_drugmaterial dd ON dd.material_id = sp.material_id						")
		   .append("	WHERE (bp.audit_status = 1 OR (bp.audit_status=2 AND bp.complain_status =1)) 			")
		   .append("	AND bp.proj_id = :projId AND sp.prod_comp_id = :compId									");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		params.put("projId", projId);
		if(StringUtils.isNotBlank(qualityspecName)){
			if(PinYin.isPinYin(qualityspecName)){
				sql.append(" and sd.qualityspec_name_spel like :qualityspecName ");
			}else{
				sql.append(" and sd.qualityspec_name like :qualityspecName ");
			}
			params.put("qualityspecName", "%"+qualityspecName+"%");
		}
		if(StringUtils.isNotBlank(qualityspecCode)){
			sql.append(" and sd.qualityspec_code = :qualityspecCode ");
			params.put("qualityspecCode", qualityspecCode);
		}
		sql.append(" order by bp.encrypt_price desc ");
		

		return sqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 获取未解密价格药品列表
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getDrugPriceEncrypList(Pagination page, SysUser user) {
		String qualityspecName = page.getConditions().get("qualityspecName");
		String qualityspecCode = page.getConditions().get("qualityspecCode");
		String projId = page.getConditions().get("projId");
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT bp.proj_proddrug_id projProddrugId,bp.proj_id projId,							")
		   .append("    DATE_FORMAT(bp.decrypt_datetime, '%Y-%m-%d %H:%i:%S' )	decryptDatetime,				")
		   .append("    sd.qualityspec_name qualityspecName,bp.decrypt_price decryptPrice,              		")
		   .append("	sd.qualityspec_code qualityspecCode,ddf.form_name formName,dds.spec_name specName,		")
		   .append("	sp.factor factor,dp.packunit_name packunitName,dd.material_name materialName, 			")
		   .append("    bp.encrypt_price encryptPrice,bp.decrypt_status decryptStatus FROM bid_projproddrug bp	")
		   .append("	LEFT JOIN std_proddruginfo sp ON bp.prod_id=sp.prod_id 									")
		   .append("	LEFT JOIN std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id				")
		   .append("	LEFT JOIN dic_drugspec dds ON dds.spec_id = sd.spec										")
		   .append("	LEFT JOIN dic_drugform ddf ON ddf.form_id = sd.form										")
		   .append("	LEFT JOIN dic_packunit dp ON dp.packunit_id = sp.unit									")
		   .append("	LEFT JOIN dic_drugmaterial dd ON dd.material_id = sp.material_id						")
		   .append("	WHERE (bp.audit_status = 1 OR (bp.audit_status=2 AND bp.complain_status =1)) 			")
		   .append("	AND bp.proj_id = :projId AND sp.prod_comp_id = :compId	and   bp.decrypt_status<>2		");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		params.put("projId", projId);
		if(StringUtils.isNotBlank(qualityspecName)){
			if(PinYin.isPinYin(qualityspecName)){
				sql.append(" and sd.qualityspec_name_spel like :qualityspecName ");
			}else{
				sql.append(" and sd.qualityspec_name like :qualityspecName ");
			}
			params.put("qualityspecName", "%"+qualityspecName+"%");
		}
		if(StringUtils.isNotBlank(qualityspecCode)){
			sql.append(" and sd.qualityspec_code = :qualityspecCode ");
			params.put("qualityspecCode", qualityspecCode);
		}
		sql.append(" order by bp.encrypt_price desc ");
		

		return sqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 保存报价
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination saveEncryptPrice(Pagination page){
		Session session = factory.getCurrentSession();
		SysUser user = (SysUser) page.getForm();
		StdCompinfo compInfo = user.getInfo();
		if (compInfo == null) {
			return page;
		}
		String compId = compInfo.getCompId();
		String projProddrugId = page.getConditions().get("projProddrugId");
		String encryptPrice = page.getConditions().get("encryptPrice");
		StringBuffer sql = new StringBuffer();
		sql.append("update bid_projproddrug set decrypt_status=1,encrypt_price = :encryptPrice,upd_user= :compId,upd_datetime= sysdate() where proj_proddrug_id= :projProddrugId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("compId", compId);
		query.setString("encryptPrice", encryptPrice);
		query.setString("projProddrugId", projProddrugId);
		query.executeUpdate();
		session.flush();
		writeLog(page);
		return page;
	}
	
	public void writeLog(Pagination page){
		Session session = factory.getCurrentSession();
		String projProddrugId = page.getConditions().get("projProddrugId");
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into bid_projproddrug_log select uuid(),dp.* from bid_projproddrug dp where dp.proj_proddrug_id= :projProddrugId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("projProddrugId", projProddrugId);
		query.executeUpdate();
		session.flush();
	}
	

	/**
	 * @category 验证密码
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination validatePassword(Pagination page){
		return page;
	}
	
	/**
	 * @category 获取报价密钥
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getPassword(Pagination page){
		Session session = factory.getCurrentSession();
		String projId = page.getConditions().get("projId");
		String compId = page.getConditions().get("compId");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT price_key FROM bid_projprodcompany WHERE proj_id = :projId AND prod_comp_id = :compId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("projId", projId);
		query.setString("compId", compId);
		page.setRows(query.list());
		return page;
	}
	
	/**
	 * @category 获取解密数据
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getDecryptData(Pagination page) {
		String projId = page.getConditions().get("projId");
		String datetime = page.getConditions().get("datetime");
		StdCompinfo compInfo = ((SysUser) page.getForm()).getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT bp.proj_proddrug_id projProddrugId,bp.proj_id projId,							")
		   .append("    DATE_FORMAT(bp.decrypt_datetime, '%Y-%m-%d %H:%i:%S' )	decryptDatetime,				")
		   .append("    sd.qualityspec_name qualityspecName,bp.decrypt_price decryptPrice,              		")
		   .append("	sd.qualityspec_code qualityspecCode,ddf.form_name formName,dds.spec_name specName,		")
		   .append("	sp.factor factor,dp.packunit_name packunitName,dd.material_name materialName, 			")
		   .append("    bp.encrypt_price encryptPrice,bp.decrypt_status decryptStatus FROM bid_projproddrug bp	")
		   .append("	LEFT JOIN std_proddruginfo sp ON bp.prod_id=sp.prod_id 									")
		   .append("	LEFT JOIN std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id				")
		   .append("	LEFT JOIN dic_drugspec dds ON dds.spec_id = sd.spec										")
		   .append("	LEFT JOIN dic_drugform ddf ON ddf.form_id = sd.form										")
		   .append("	LEFT JOIN dic_packunit dp ON dp.packunit_id = sp.unit									")
		   .append("	LEFT JOIN dic_drugmaterial dd ON dd.material_id = sp.material_id						")
		   .append("	WHERE (bp.audit_status = 1 OR (bp.audit_status=2 AND bp.complain_status =1)) 			")
		   .append("	AND bp.proj_id = :projId AND sp.prod_comp_id = :compId	and   bp.decrypt_status=2		")
		   .append("    and bp.decrypt_datetime >=:datetime    order by bp.decrypt_datetime asc                 ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		params.put("projId", projId);
		params.put("datetime", datetime);
		page.setRows(sqlUtil.getSqlMapList(sql.toString(), params));
		return page;
	}
	
	/**
	 * @category 获取要解密的数据
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getStartDecryptData(Pagination page) {
		String projId = page.getConditions().get("projId");
		StdCompinfo compInfo = ((SysUser) page.getForm()).getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT bp.proj_proddrug_id projProddrugId,bp.proj_id projId,							")
		   .append("    DATE_FORMAT(bp.decrypt_datetime, '%Y-%m-%d %H:%i:%S' )	decryptDatetime,				")
		   .append("    sd.qualityspec_name qualityspecName,bp.decrypt_price decryptPrice,              		")
		   .append("	sd.qualityspec_code qualityspecCode,ddf.form_name formName,dds.spec_name specName,		")
		   .append("	sp.factor factor,dp.packunit_name packunitName,dd.material_name materialName, 			")
		   .append("    bp.encrypt_price encryptPrice,bp.decrypt_status decryptStatus FROM bid_projproddrug bp	")
		   .append("	LEFT JOIN std_proddruginfo sp ON bp.prod_id=sp.prod_id 									")
		   .append("	LEFT JOIN std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id				")
		   .append("	LEFT JOIN dic_drugspec dds ON dds.spec_id = sd.spec										")
		   .append("	LEFT JOIN dic_drugform ddf ON ddf.form_id = sd.form										")
		   .append("	LEFT JOIN dic_packunit dp ON dp.packunit_id = sp.unit									")
		   .append("	LEFT JOIN dic_drugmaterial dd ON dd.material_id = sp.material_id						")
		   .append("	WHERE (bp.audit_status = 1 OR (bp.audit_status=2 AND bp.complain_status =1)) 			")
		   .append("	AND bp.proj_id = :projId AND sp.prod_comp_id = :compId	and   bp.decrypt_status=1		");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);
		params.put("projId", projId);
		page.setRows(sqlUtil.getSqlMapList(sql.toString(), params));
		return page;
	}
	
	/**
	 * @category 开始解密价格
	 * @date 2015年8月27日
	 * @param page
	 * @return
	 */
	@Override
	public Pagination startDecryptPrice(Pagination page){
		Session session = factory.getCurrentSession();
		String projProddrugId = page.getConditions().get("projProddrugId");
		String decryptPrice = page.getConditions().get("decryptPrice");
		String compId = page.getConditions().get("compId");
		String decryptDatetime = page.getConditions().get("decryptDatetime");
		StringBuffer sql = new StringBuffer();
		sql.append("update bid_projproddrug set decrypt_price=:decryptPrice,decrypt_datetime=:decryptDatetime,decrypt_status=2,upd_datetime=sysdate(),upd_user=:compId where proj_proddrug_id=:projProddrugId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("decryptPrice", decryptPrice);
		query.setString("decryptDatetime", decryptDatetime);
		query.setString("compId", compId);
		query.setString("projProddrugId", projProddrugId);
		query.executeUpdate();
		session.flush();
		writeLog(page);
		return page;
	}
	
	/**
	 * @category 解密价格公示列表
	 * @date 2015年8月26日
	 * @return
	 */
	@Override
	public Pagination getDrugPriceDecryptPubList(Pagination page){
		String qualityspecName = page.getConditions().get("qualityspecName");
		String qualityspecCode = page.getConditions().get("qualityspecCode");
		String projId = page.getConditions().get("projId");
		String prodCompName = page.getConditions().get("prodCompName");
		StringBuffer sql = new StringBuffer();
		sql.append("	SELECT																			")
           .append("	bp.proj_proddrug_id projProddrugId,												")
           .append("	bp.proj_id projId,																")
           .append("	DATE_FORMAT(bp.decrypt_datetime,'%Y-%m-%d %H:%i:%S' ) decryptDatetime, 			")
           .append("	sd.qualityspec_name qualityspecName,											")
           .append("	bp.decrypt_price decryptPrice,													")
           .append("	sd.qualityspec_code qualityspecCode,											")
           .append("	ddf.form_name formName,															")
           .append("	dds.spec_name specName,															")
           .append("	sp.factor factor,																")
           .append("	dp.packunit_name packunitName,													")
           .append("	dd.material_name materialName,													")
           .append("	sc.comp_name prodCompName,														")
           .append("	bp.decrypt_status decryptStatus ,												")
           .append("	bpt.name  projName																")
           .append("	FROM  bid_projproddrug        bp    											")
           .append("	LEFT JOIN bid_project         bpt ON bpt.proj_id = bp.proj_id					")
           .append("	LEFT JOIN std_proddruginfo    sp ON bp.prod_id=sp.prod_id 						")         
           .append("	LEFT JOIN std_compinfo 		  sc ON sc.comp_id = sp.prod_comp_id   				")     
           .append("	LEFT JOIN std_drugqualityspec sd ON sd.qualityspec_id = sp.qualityspec_id   	")       
           .append("	LEFT JOIN dic_drugspec        dds ON dds.spec_id = sd.spec   					")              
           .append("	LEFT JOIN  dic_drugform       ddf ON ddf.form_id = sd.form   					")                
           .append("	LEFT JOIN dic_packunit        dp ON dp.packunit_id = sp.unit  					")             
           .append("	LEFT JOIN dic_drugmaterial    dd ON dd.material_id = sp.material_id    			")             
           .append(" 	WHERE (bp.audit_status = 1 OR (bp.audit_status=2 AND bp.complain_status =1))	");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(qualityspecName)){
			if(PinYin.isPinYin(qualityspecName)){
				sql.append(" and sd.qualityspec_name_spel like :qualityspecName ");
			}else{
				sql.append(" and sd.qualityspec_name like :qualityspecName ");
			}
			params.put("qualityspecName", "%"+qualityspecName+"%");
		}
		if(StringUtils.isNotBlank(qualityspecCode)){
			sql.append(" and sd.qualityspec_code = :qualityspecCode ");
			params.put("qualityspecCode", qualityspecCode);
		}
		if(StringUtils.isNotBlank(prodCompName)){
			if(PinYin.isPinYin(prodCompName)){
				sql.append(" and sc.comp_spel_code like :prodCompName ");
			}else{
				sql.append(" and sc.comp_name like :prodCompName ");
			}
			params.put("prodCompName", "%"+prodCompName+"%");
		}
		if(StringUtils.isNotBlank(projId)){
			sql.append(" and bp.proj_id = :projId ");
			params.put("projId", projId);
		}
		sql.append("  ORDER BY bp.prod_id DESC ");
		return sqlUtil.getSqlPageMapList(sql.toString(), params, page);
	}
	
}
